One-to-Many Relationship Setup

Set up a one-to-many relationship between todos and todo lists using SQLAlchemy ORM

Now that we've reviewed how to use db.relationship and db.ForeignKey to set up relationships between models, let's focus back on our To-Do App and use these concepts to model To-Do Lists in our app and set up the relationship between our To-Do model and our new To-Do List model.

To-Do Lists have many To-Dos, and every To-Do belongs to exactly one To-Do List, which indicates the existence of a one to many relationship between To-Dos and To-Do Lists.

(For reference: read "The 3 Types of Relationships in Database Design" by Database.Guide -- click here )

Follow along! Interactive workspace

You can use the interactive workspace below to follow along the walk-through videos below. We'll be building out the To-Do List model.

Starter Code

Download here: todoapp-updates-delete.zip

To run (in debug mode):

$ pip3 install -r requirements.txt
$ FLASK_APP=app.py FLASK_DEBUG=true flask run

Creating the TodoList model and adding the foreign key to the child Todo model

Create and run a migration to upgrade the schema

Overall steps taken

• Modified our Todo model to (temporarily) allow null values in list_id :

  list_id = db.Column(db.Integer, db.ForeignKey('todolists.id'), nullable=True)

• Ran the migration, allowing list_id to be null

Then using psql (or any other Postgres client),

• Populated our database with a default list ("Uncategorized") to add all currently existing Todo items to
• Associated existing to-do items with the "Uncategorized" list with ID 1, setting todo.list_id = 1. We could have also done this in a migration rather than using psql; either works.
• Set nullable=False on the list_id column
• Ran flask db migrate to generate a migration file for updating the nullability constraint
• Ran flask db upgrade to apply the migration

Example Terminal Output

macbook:~ amy$ psql todoapp
psql (11.3, server 10.10)
Type "help" for help.
todoapp=# \dt
            List of relations
 Schema |      Name       | Type  | Owner
--------+-----------------+-------+-------
 public | alembic_version | table | amy
 public | todolists       | table | amy
 public | todos           | table | amy
(3 rows)

todoapp=# insert into todolists (name) values ('Uncategorized');
INSERT 0 1
todoapp=# update todos set list_id = 1 where list_id is null;
UPDATE 6
todoapp=# select * from todos;
 id | description  | completed | list_id
----+--------------+-----------+---------
  5 | Do homework  | f         |       1
  7 | Do thing 3   | f         |       1
 21 | thing 17     | t         |       1
  6 | Do a thing   | t         |       1
  8 | Do a thing 4 | t         |       1
  9 | Thing 5      | f         |       1
(6 rows)

todoapp=# \q

Aside

SQL commands can be written in any case ( update , UPDATE , uPDaTe ) and they will still execute.

Important

• We always want to use migrations in order to update the data schema.
• We can establish maintenance windows during times when the app isn't well used and manipulate production data then, in order to prepare the data before a schema migration, and change it after a schema migration.